package cn.lgp6.ziyou_util.excel.service.impl;

import cn.lgp6.ziyou_util.common.result.RequestResult;
import cn.lgp6.ziyou_util.common.result.RequestResultBuilder;
import cn.lgp6.ziyou_util.excel.entity.vo.ExcelToMysqlFormVO;
import cn.lgp6.ziyou_util.excel.service.ExcelService;
import cn.lgp6.ziyou_util.excel.controller.ExcelWebSocketController;
import cn.lgp6.ziyou_util.excel.entity.dto.ExcelMessageDTO;
import com.alibaba.fastjson.JSON;
import com.mchange.v2.c3p0.ComboPooledDataSource;
import jxl.Sheet;
import jxl.Workbook;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.stereotype.Service;

import javax.websocket.Session;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

@Service
public  class  ExcelServiceImpl implements ExcelService {
    Logger log = LoggerFactory.getLogger(ExcelServiceImpl.class);
    @Override
    public RequestResult importExcelToMysql(ExcelToMysqlFormVO excelToMysqlFormVO) {
        Map<Session,Integer> map= ExcelWebSocketController.userMap;
        Session session=null;
        List<String> sessionIds=new ArrayList<>();
        for (Session sessionEach : map.keySet()) {
            sessionIds.add(sessionEach.getId());
            if (sessionEach.getId().equals(excelToMysqlFormVO.getSessionId())){
                session=sessionEach;
            }
        }
        if (session==null){

            log.info("会话过期,传入sessionId:"+excelToMysqlFormVO.getSessionId()+",列表已有sessionId:"+JSON.toJSONString(sessionIds));
            return RequestResultBuilder.error("会话已过期,请刷新重试  ");
        }
        Integer integer = map.get(session);
        for (Session s : map.keySet()) {
            if (map.get(s)<integer){
                log.info(session.getId()+"-"+map.get(session)+":非法请求");
                return RequestResultBuilder.error("非法请求");
            }
        }
        Integer id = map.get(session);
        log.info("=========开始======="+id+"===================");
        Connection connection=null;
        Statement statement=null;
        ResultSet resultSet=null;
        Long count=0L;
        try {
            Workbook workbook=Workbook.getWorkbook(excelToMysqlFormVO.getFile().getInputStream());
            Sheet[] sheets = workbook.getSheets();
            ExcelMessageDTO messageDTO=new ExcelMessageDTO();
            messageDTO.setType("message");
            messageDTO.setMsg(("共"+sheets.length+"张sheet页"));
            ExcelWebSocketController.sendText(session,messageDTO,"ExcelService 57");
            log.info("共"+sheets.length+"张sheet页");
            log.info("==正在连接数据库");
            messageDTO.setMsg("正在连接数据库...");
            ExcelWebSocketController.sendText(session,messageDTO,"ExcelService 61");
            ComboPooledDataSource scoure = new ComboPooledDataSource();
            scoure.setUser(excelToMysqlFormVO.getUsername());
            scoure.setPassword(excelToMysqlFormVO.getPassword());
            scoure.setDriverClass("com.mysql.jdbc.Driver");
            String url="jdbc:mysql://"+excelToMysqlFormVO.getAddress()+":"
                    +excelToMysqlFormVO.getPort()+"/"+excelToMysqlFormVO.getDatabase()
                    +"?useUnicode=true&characterEncoding=utf8&" +
                    "serverTimezone=GMT%2B8";
            scoure.setJdbcUrl(url);
            connection=scoure.getConnection();
            if (!connection.isClosed()){
                log.info("数据库连接成功");
                messageDTO.setMsg("数据库连接成功");
                ExcelWebSocketController.sendText(session,messageDTO,"ExcelService 75");
                statement = connection.createStatement();

                for (Sheet sheet : sheets) {
                    for (int j = excelToMysqlFormVO.getStartRow(); j<sheet.getRows(); j++){
                        StringBuilder sql=new StringBuilder("insert into "+excelToMysqlFormVO.getDatabase()+"."+excelToMysqlFormVO.getTableName()+" (");
                        sql.append(excelToMysqlFormVO.getColumns()).append(") values (");
                        for (int i=excelToMysqlFormVO.getStartColumn();i<sheet.getColumns();i++){
                            if (i!=sheet.getColumns()-1){
                                sql.append("'").append(sheet.getCell(i, j).getContents()).append("',");
                            }else{
                                sql.append("'").append(sheet.getCell(i, j).getContents()).append("')");
                            }
                        }
                        log.info("sql:"+sql.toString());
                        messageDTO.setMsg("生成SQL:"+sql.toString());
                        ExcelWebSocketController.sendText(session,messageDTO,"ExcelService 91");
                        statement.executeUpdate(sql.toString());
                        log.info("已增加:"+j+"次");
                        messageDTO.setMsg("已处理"+j+"条");
                        ExcelWebSocketController.sendText(session,messageDTO,"ExcelService 95");
                        count=Long.parseLong(String.valueOf(j));
                    }
                }

            }else{
                return RequestResultBuilder.fail("数据库连接被断开,connection.isClosed=true");
            }
        } catch (Exception e) {
            e.printStackTrace();
            return RequestResultBuilder.error("程序异常,异常信息:"+e.getLocalizedMessage());
        }finally {
            try {
                if (resultSet!=null){
                    resultSet.close();;
                }
                if (statement!=null){
                    statement.close();
                }
                if (connection!=null){
                    connection.close();
                }
            } catch (Exception e) {
                return RequestResultBuilder.error("数据库连接关闭失败,异常信息:"+e.getLocalizedMessage());
            }
        }
        log.info("=============结束==="+id+"===================");



        ExcelWebSocketController.onClose(session);

        return RequestResultBuilder.success("导入完成,共导入"+count+"条数据");
    }

}
